package jdbc;

import bean.User;
import utils.JDBCUtilsOld;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Date:2022/2/28
 * Author:ybc
 * Description:
 * 特殊的SQL语句：
 * 1、批量删除
 * 批量删除时若使用delete from 表名 where id in (?)
 * 此时不能使用通配符为删除的id列表赋值，否则报错Data truncation: Truncated incorrect DOUBLE value: '8,9,10'
 * 解决方案：字符串拼接
 * 2、模糊查询
 * 模糊查询若使用select * from t_user where username like '%?%'
 * 此时因为?通配符在''中，所以会被解析为字符串的一部分，而不是通配符即不能通过通配符赋值
 * 解决方案：
 * a>字符串拼接
 * b>concat('%',?,'%')-->concat('%','a','%')-->'%a%'
 * c>like "%"?"%"
 */
public class SpecialSQLTest {

    @Test
    public void testMohu(){
        String mohu = "a";
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            //方式1：
            /*String sql = "select * from t_user where username like '%"+mohu+"%'";
            ps = connection.prepareStatement(sql);*/
            //方式2：
            /*String sql = "select * from t_user where username like concat('%',?,'%')";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, mohu);*/
            //方式3：
            String sql = "select * from t_user where username like \"%\"?\"%\"";
            ps = connection.prepareStatement(sql);
            ps.setObject(1, mohu);
            rs = ps.executeQuery();
            List<User> list = new ArrayList<>();
            while (rs.next()){
                User user = new User();
                user.setId(rs.getInt(1));
                user.setUsername(rs.getString(2));
                user.setPassword(rs.getString(3));
                user.setAge(rs.getInt(4));
                user.setSex(rs.getString(5));
                user.setEmail(rs.getString(6));
                list.add(user);
            }
            list.forEach(System.out::println);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
    }

    @Test
    public void testDeleteMore(){
        String ids = "8,9,10";
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            String sql = "delete from t_user where id in ("+ids+")";
            ps = connection.prepareStatement(sql);
            int result = ps.executeUpdate();
            System.out.println("结果："+result);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
    }

}
